﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LotteryVote.Entity;
using System.Data.SqlClient;

namespace LotteryVote.Data
{
    public class BetSheetDataAccess : DataBase
    {
        public void InsertBetSheet(BetSheet sheet)
        {
            string sql = string.Format(@"INSERT INTO {0} ({1},{2},{3},{4}) VALUES (@{1},@{2},@{3},@{4}) SELECT IDENT_CURRENT('{0}')",
                BetSheet.TABLENAME, BetSheet.USERID, BetSheet.NUM, BetSheet.STATUS, BetSheet.IPADDRESS);
            object id = base.ExecuteScalar(sql, new SqlParameter(BetSheet.USERID, sheet.UserId),
                new SqlParameter(BetSheet.NUM, sheet.Num),
                new SqlParameter(BetSheet.STATUS, (int)sheet.Status),
                new SqlParameter(BetSheet.IPADDRESS, sheet.IPAddress));
            sheet.SheetId = Convert.ToInt32(id);
        }
        public void UpdateBetSheetStatus(int sheetId, BetStatus status)
        {
            string sql = string.Format(@"UPDATE {0} SET {1}=@{1} WHERE {2}=@{2}", BetSheet.TABLENAME, BetSheet.STATUS, BetSheet.SHEETID);
            base.ExecuteNonQuery(sql, new SqlParameter(BetSheet.STATUS, (int)status),
                new SqlParameter(BetSheet.SHEETID, sheetId));
        }

        public BetSheet GetBetSheet(int sheetId)
        {
            string sql = string.Format(@"SELECT * FROM {0} WHERE {1}=@{1}", BetSheet.TABLENAME, BetSheet.SHEETID);
            return base.ExecuteEntity<BetSheet>(sql, new SqlParameter(BetSheet.SHEETID, sheetId));
        }
        public IList<BetSheet> ListBetSheet(int userId)
        {
            string sql = string.Format(@"select bs.*,bo.{2},bo.{3},bo.{4} from {0} bs
inner join (select {5} as {6},SUM({2}) as {2},
Sum({3}) as {3},SUM({4}) as {4} from {1} group by {5}) bo
on bo.{5}=bs.{6}
where bs.{7}=@{7}", BetSheet.TABLENAME, BetOrder.TABLENAME, BetOrder.TURNOVER, BetOrder.COMMISSION, BetOrder.NETAMOUNT,
                    BetOrder.SHEETID, BetSheet.SHEETID, BetSheet.USERID);
            return base.ExecuteList<BetSheet>(sql, new SqlParameter(BetSheet.USERID, userId));
        }
        public IList<BetSheet> ListBetSheet(int userId, DateTime date)
        {
            string sql = string.Format(@"select bs.*,bo.{2},bo.{3},bo.{4} from {0} bs
inner join (select {5} as {6},SUM({2}) as {2},
Sum({3}) as {3},SUM({4}) as {4} from {1} group by {5}) bo
on bo.{5}=bs.{6}
where bs.{7}=@{7} and DAY(bs.{8})=DAY(@{8})", BetSheet.TABLENAME, BetOrder.TABLENAME, BetOrder.TURNOVER, BetOrder.COMMISSION, BetOrder.NETAMOUNT,
                    BetOrder.SHEETID, BetSheet.SHEETID, BetSheet.USERID, BetSheet.CREATETIME);
            return base.ExecuteList<BetSheet>(sql, new SqlParameter(BetSheet.USERID, userId),
                new SqlParameter(BetSheet.CREATETIME, date));
        }
        public IList<BetSheet> ListBetSheet(int userId, DateTime date, int pageCount, int pageIndex)
        {
            int preCount = pageCount * (pageIndex - 1) + 1;
            string sql = string.Format(@";WITH CTE AS(select bs.*,bo.{2},bo.{3},bo.{4} from {0} bs
inner join (select {5} as {6},SUM({2}) as {2},
Sum({3}) as {3},SUM({4}) as {4} from {1} group by {5}) bo
on bo.{5}=bs.{6}
where bs.{7}=@{7} and DAY(bs.{8})=DAY(@{8}))
SELECT TOP {9} *
FROM CTE
WHERE ({6} >=
          (SELECT MAX({6})
         FROM (SELECT TOP {10} {6}
                 FROM CTE
                 ORDER BY {6}) AS T))
ORDER BY {6}", BetSheet.TABLENAME, BetOrder.TABLENAME, BetOrder.TURNOVER, BetOrder.COMMISSION, BetOrder.NETAMOUNT,
                    BetOrder.SHEETID, BetSheet.SHEETID, BetSheet.USERID, BetSheet.CREATETIME, pageCount, preCount);
            return base.ExecuteList<BetSheet>(sql, new SqlParameter(BetSheet.USERID, userId),
                new SqlParameter(BetSheet.CREATETIME, date));
        }
        public IList<BetSheet> ListBetSheet(BetStatus status, DateTime date, int pageCount, int pageIndex)
        {
            string sql = string.Format(@";WITH CTE AS(
select bs.*,bo.Commission,bo.NetAmount,bo.Turnover,u.RoleId,u.ParentId,u.UserName from {0} bs
inner join (select SheetId as SheetId,SUM(Turnover) as Turnover,
Sum(Commission) as Commission,SUM(NetAmount) as NetAmount from tb_BetOrder group by SheetId) bo
on bo.SheetId=bs.SheetId
join {1} u on u.{2}=bs.{3})
SELECT * FROM 
(
     SELECT ROW_NUMBER() OVER(ORDER BY SheetId DESC) AS RowNumber,* 
     FROM CTE
) T
WHERE {4}=@{4} And DAY({5})=DAY(@{5}) AND RowNumber BETWEEN {6} AND {7}", BetSheet.TABLENAME, User.TABLENAME, User.USERID, BetSheet.USERID,
                BetSheet.STATUS, BetSheet.CREATETIME, (pageIndex - 1) * pageCount + 1, pageIndex * pageCount);
            return base.ExecuteList<BetSheet>(sql, new SqlParameter(BetSheet.STATUS, (int)status),
                new SqlParameter(BetSheet.CREATETIME, date));
        }
        public int CountBetSheet(int userId, DateTime date)
        {
            string sql = string.Format(@"SELECT COUNT(0) FROM {0} WHERE {1}=@{1} AND DAY({2})=DAY(@{2})", BetSheet.TABLENAME, BetSheet.USERID, BetSheet.CREATETIME);
            object count = base.ExecuteScalar(sql, new SqlParameter(BetSheet.USERID, userId),
                new SqlParameter(BetSheet.CREATETIME, date));
            return Convert.ToInt32(count);
        }
        public int CountBetSheet(BetStatus status, DateTime date)
        {
            string sql = string.Format(@"SELECT COUNT(0) FROM {0} WHERE {1}=@{1} AND {2}=@{2}", BetSheet.TABLENAME, BetSheet.STATUS, BetSheet.CREATETIME);
            object count = base.ExecuteScalar(sql, new SqlParameter(BetSheet.STATUS, (int)status),
                new SqlParameter(BetSheet.CREATETIME, date));
            return Convert.ToInt32(count);
        }
    }
}
